package sy.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import sy.pageModel.DataGrid;
import sy.pageModel.Member;
import sy.pageModel.OrderDoctor;
import sy.pageModel.OrderGoods;
import sy.service.OrderGoodsServiceI;
import sy.util.DateTimeUtility;
import sy.util.DateUtil;
import sy.util.StringUtil;

@Service("orderGoodsService")
public class OrderGoodsServiceImpl extends BaseService implements OrderGoodsServiceI {

	@Override
	public DataGrid datagrid(OrderGoods order) throws Exception {
		DataGrid dg = new DataGrid();
		String sql = "select count(*) from xj_shop_order a, view_hy b where a.userid=b.vnumber";
		sql = addWhere(order, sql);
		System.out.println(sql);
		long total = template.queryForLong(sql);
		System.out.println(total);
		dg.setTotal(total);
		
		sql = "select a.id, userid, a.deptid, a.address, c.name as depts, mainid, a.typeid, d.names as goodsName, d.shop_type as setId, d.descs as goodsUnit, d.year, e.descs as setName, dj_price, ys_money, sj_money, service_detail, sq_time, state, counts, xm, sjh "
		      + "from xj_shop_order a, view_hy b, p_info c, p_goods d, s_code e where a.userid=b.vnumber and a.deptid=c.id and a.typeid=d.id and d.shop_type=e.code and e.types='商品系列'";
		sql = addWhere(order, sql);
		sql = addOrderBy(order, sql);
		sql = addLimit(order, sql);
		System.out.println(sql);
		final List<OrderGoods> orderList = new ArrayList<OrderGoods>();
        template.query(sql, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
            	OrderGoods order = new OrderGoods();
                order.setId(rs.getString("id"));
                order.setOrderId(rs.getString("id"));
                order.setCardId(rs.getString("userid"));
                order.setAgencyId(rs.getString("deptid"));
                order.setAgencyName(rs.getString("depts"));
                order.setGoodsId(rs.getString("typeid"));
                order.setGoodsName(rs.getString("goodsName"));
                order.setGoodsCount(rs.getInt("counts"));
                order.setGoodsType(rs.getString("mainid"));
                order.setGoodsFee(rs.getDouble("dj_price"));
                order.setTotalFee(rs.getDouble("ys_money"));
                order.setActualFee(rs.getDouble("sj_money"));
                order.setServiceDetails(rs.getString("service_detail"));
                order.setCreateTime(rs.getString("sq_time"));
                order.setStatus(rs.getString("state"));
                order.setName(rs.getString("xm"));
                order.setMobile(rs.getString("sjh"));
                order.setSetId(rs.getString("setId"));
                order.setSetName(rs.getString("setName"));
                order.setUnit(rs.getString("goodsUnit"));
                order.setAddressId(rs.getString("address"));
                order.setYear(rs.getString("year"));
            	orderList.add(order);
            }});
		dg.setRows(orderList);		
		return dg;
	}
	
	@Override
	public DataGrid datagridForAccepted(OrderGoods order) throws Exception {
		DataGrid dg = new DataGrid();
		String sql = "select count(*) from xj_shop_order a, view_hy b where a.userid=b.vnumber and state='" + OrderDoctor.ORDER_STATUS_ID_ACCEPTED + "'";
		sql = addWhere(order, sql);
		System.out.println(sql);
		long total = template.queryForLong(sql);
		System.out.println(total);
		dg.setTotal(total);
		
		sql = "select a.id, userid, a.deptid, a.address, c.name as depts, mainid, a.typeid, d.names as goodsName, d.shop_type as setId, d.descs as goodsUnit, d.year, e.descs as setName, dj_price, ys_money, sj_money, service_detail, sq_time, state, counts, xm, sjh "
		      + "from xj_shop_order a, view_hy b, p_info c, p_goods d, s_code e where a.userid=b.vnumber and a.deptid=c.id and a.typeid=d.id and d.shop_type=e.code and e.types='商品系列' and state='" + OrderDoctor.ORDER_STATUS_ID_ACCEPTED + "'";
		sql = addWhere(order, sql);
		sql = addOrderBy(order, sql);
		sql = addLimit(order, sql);
		System.out.println(sql);
		final List<OrderGoods> orderList = new ArrayList<OrderGoods>();
        template.query(sql, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
            	OrderGoods order = new OrderGoods();
                order.setId(rs.getString("id"));
                order.setOrderId(rs.getString("id"));
                order.setCardId(rs.getString("userid"));
                order.setAgencyId(rs.getString("deptid"));
                order.setAgencyName(rs.getString("depts"));
                order.setGoodsId(rs.getString("typeid"));
                order.setGoodsName(rs.getString("goodsName"));
                order.setGoodsCount(rs.getInt("counts"));
                order.setGoodsType(rs.getString("mainid"));
                order.setGoodsFee(rs.getDouble("dj_price"));
                order.setTotalFee(rs.getDouble("ys_money"));
                order.setActualFee(rs.getDouble("sj_money"));
                order.setServiceDetails(rs.getString("service_detail"));
                order.setCreateTime(rs.getString("sq_time"));
                order.setStatus(rs.getString("state"));
                order.setName(rs.getString("xm"));
                order.setMobile(rs.getString("sjh"));
                order.setSetId(rs.getString("setId"));
                order.setSetName(rs.getString("setName"));
                order.setUnit(rs.getString("goodsUnit"));
                order.setAddressId(rs.getString("address"));
                order.setYear(rs.getString("year"));
            	orderList.add(order);
            }});
		dg.setRows(orderList);		
		return dg;
	}
	
	private String addWhere(OrderGoods order, String sql) {
		if(!StringUtil.isNullOrEmpty(order.getOrderId())) {
			sql += " and a.id like '%" + order.getOrderId() + "%'";
		}
		if(!StringUtil.isNullOrEmpty(order.getCardId())) {
			sql += " and userid like '%" + order.getCardId() + "%'";
		}
		if(!StringUtil.isNullOrEmpty(order.getName())) {
			sql += " and xm like '%" + order.getName() + "%'";
		}
		if(!StringUtil.isNullOrEmpty(order.getMobile())) {
			sql += " and sjh like '%" + order.getMobile() + "%'";
		}
		if(!StringUtil.isNullOrEmpty(order.getCreateTimeStart())) {
			sql += " and sq_time >= '" + order.getCreateTimeStart() + "'";
		}
		if(!StringUtil.isNullOrEmpty(order.getCreateTimeEnd())) {
			sql += " and sq_time <= '" + order.getCreateTimeEnd() + "'";
		}
		return sql;
	}
	
	private String addLimit(OrderGoods order, String sql) {
		int fromRow = (order.getPage() - 1) * order.getRows();
	    sql += " limit " + fromRow + "," + order.getRows();
	    return sql;
	}
	
	private String addOrderBy(OrderGoods order, String sql) {
		if (order.getSort() != null) {
			if("cardId".equals(order.getSort())) {
				order.setSort("userid");
			} else if("name".equals(order.getSort())) {
				order.setSort("convert(xm using gbk)");
			} else if("createTime".equals(order.getSort())) {
				order.setSort("sq_time");
			} else if("mobile".equals(order.getSort())) {
				order.setSort("sjh");
			} else if("orderId".equals(order.getSort())) {
				order.setSort("id");
			} else if("statusName".equals(order.getSort())) {
				order.setSort("state");
			} else if("agencyName".equals(order.getSort())) {
				order.setSort("depts");
			} else if("goodsName".equals(order.getSort())) {
				order.setSort("goodsName");
			} else if("goodsTypeName".equals(order.getSort())) {
				order.setSort("mainid");
			} else if("goodsCount".equals(order.getSort())) {
				order.setSort("counts");
			} else if("goodsFee".equals(order.getSort())) {
				order.setSort("dj_price");
			} else if("totalFee".equals(order.getSort())) {
				order.setSort("ys_money");
			} else if("setName".equals(order.getSort())) {
				order.setSort("setName");
			}
			sql += " order by " + order.getSort() + " " + order.getOrder();
		}
		return sql;
	}
	
	@Override
	@Transactional
	public OrderGoods add(OrderGoods order) throws Exception {
		System.out.println(order);
	    //得到商品价钱等信息
	    Map<String, Object> infoMap = getGoodsInfo(order.getGoodsId());
	    if(infoMap.isEmpty()) {
	    	throw new Exception("获取商品信息失败！");
	    }
	    
	    order.setGoodsFee((Double)infoMap.get("price" + order.getMemberType()));
	    order.setTotalFee(order.getGoodsFee() * order.getGoodsCount());
	    order.setGoodsName((String)infoMap.get("goodsName"));
	    order.setStatus(OrderDoctor.ORDER_STATUS_ID_WAIT_ACCEPT);
	    
	    Date now = new Date();
	    order.setCreateTime(DateUtil.format(now));
	    String orderId = "S-" + order.getCardId() + "-" + DateUtil.format(now, "yyyyMMddHHmmss");
	    order.setOrderId(orderId);
	    order.setId(orderId);
	    
	    System.out.println(order);
	    
	    String sql = "insert into xj_shop_order (id,names,userid,typeid,deptid,sq_time,ys_money,counts,state,dj_price,address) " + 
	             "values(?,?,?,?,?,?,?,?,?,?,?)";
	
		Object[] paras = new Object[] {
				  order.getId(),
				  order.getGoodsName(),
				  order.getCardId(),
				  order.getGoodsId(),
				  order.getAgencyId(),
				  order.getCreateTime(),
				  order.getTotalFee(),
				  order.getGoodsCount(),
				  order.getStatus(),
				  order.getGoodsFee(),
				  order.getAddressId()
		};
		
		int affectRow = 0;
		try {
		    affectRow = template.update(sql, paras);
		} catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException("添加订单失败，请联系系统管理员！");
		}
		if(affectRow == 0) {
			throw new RuntimeException("添加订单失败，请联系系统管理员！");
		}
	
	    return order;
	}
	
	@Override
	@Transactional
	public OrderGoods edit(OrderGoods order) throws Exception {
		String sql = "update xj_shop_order set sj_money=?,service_detail=?,state=?,address=?" 
		             + " where id=?";
		System.out.println(sql);
		order.setStatus(orderStatus(order.getStatus()));
		Object[] paras = new Object[] {
		    order.getActualFee(),
		    order.getServiceDetails(),
		    order.getStatus(),
		    order.getAddressId(),
		    order.getOrderId()
		};
		editOrderBySql(sql,paras);
		updateMenber(order.getCardId(),order.getOrderId(),order.getActualFee(),order.getStatus());
		return order;
	}
	
	@Override
	@Transactional
	public OrderGoods addDetails(OrderGoods order) throws Exception {
		System.out.println(order);
		int affectRow = 0;
		String sql = "update xj_shop_order set sj_money=?,service_detail=?,state=?" 
		             + " where id=?";
		System.out.println(sql);
		Object[] paras = new Object[] {
		    order.getActualFee(),
		    order.getServiceDetails(),
		    order.getStatus(),
		    order.getOrderId()
		};
		try {
		    affectRow = template.update(sql, paras);
		} catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException("生成详单失败，请联系系统管理员！");
		}
		if(affectRow == 0) {
			throw new RuntimeException("订单记录不存在！");
		} else if(affectRow > 1) {
			throw new RuntimeException("存在多条订单记录，生成详单失败，请联系系统管理员！");
		}
		return order;
	}
	
	@Override
	public Map<String, String> getAgenciesByType(String goodsType) throws Exception {
		final Map<String, String> result = new LinkedHashMap<String, String>();
		String sql = "select id, name from p_info where mainid=?";
		System.out.println(sql);
		Object[] paras = new Object[] {
				goodsType
	    };
		template.query(sql, paras, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
            	result.put(rs.getString("id"), rs.getString("name"));
            }
        });
		return result;
	}
	
	@Override
	public Map<String, String> getGoodsByAgencyId(String agencyId) throws Exception {
		final Map<String, String> result = new LinkedHashMap<String, String>();
		String sql = "select a.id, a.names, b.descs as setName, a.descs as unit, a.year from p_goods a,s_code b where a.deptid=? and a.shop_type=b.code and b.types='商品系列' order by b.code,a.descs,a.year";
		System.out.println(sql);
		Object[] paras = new Object[] {
				agencyId
	    };
		template.query(sql, paras, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
            	result.put(rs.getString("id"), rs.getString("setName") + " " + rs.getString("names") + " " + rs.getString("unit") + " " + rs.getString("year"));
            }
        });
		return result;
	}
	
	private Map<String, Object> getGoodsInfo(String goodsId) throws Exception {
		final Map<String, Object> result = new HashMap<String, Object>();
		String sql = "select names, price1, price2, price3, price4 from p_goods where id=?";
		System.out.println(sql);
		Object[] paras = new Object[] {
				goodsId
	    };
		template.query(sql, paras, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
            	result.put("goodsName", rs.getString("names"));
            	result.put("price" + Member.MEMBER_TYPE_ID_BASE, rs.getDouble("price2"));
            	result.put("price" + Member.MEMBER_TYPE_ID_MAINTAIN, rs.getDouble("price3"));
            	result.put("price" + Member.MEMBER_TYPE_ID_ADVANCE, rs.getDouble("price4"));
            	result.put("price" + Member.MEMBER_TYPE_ID_HONOR, rs.getDouble("price3"));
            	result.put("price" + Member.MEMBER_TYPE_ID_EXPERIENCE, rs.getDouble("price1"));
            }
        });
		return result;
	}
	
	@Override
	public Map<String, String> getAddressesByCardId(String cardId) throws Exception {
		final Map<String, String> result = new LinkedHashMap<String, String>();
		String sql = "select a.id,rname,mobile,b.descs as province,c.descs as city, d.descs as area, addre from u_user_info a, s_code_province b, s_code_city c, s_code_area d where a.province=b.code and a.city=c.code and a.area=d.code and a.userid=?";
		System.out.println(sql);
		Object[] paras = new Object[] {
				cardId
	    };
		template.query(sql, paras, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
            	result.put(rs.getString("id"), rs.getString("rname") + " " + rs.getString("mobile") + " " + rs.getString("province") + " " + rs.getString("city") + " " + rs.getString("area") + " " + rs.getString("addre"));
            }
        });
		return result;
	}

	@Override
	@Transactional
	public OrderGoods editGoodsStatus(OrderGoods order) {
		String sql = "update xj_shop_order set state=? where id=?";
		System.out.println(sql);
		order.setStatus(OrderDoctor.ORDER_STATUS_ID_CANCELLED);
		Object[] paras = new Object[] {
				order.getStatus(),
				order.getOrderId()
		};
		editOrderBySql(sql,paras);
		return order;
	}

	@Override
	@Transactional
	public OrderGoods saveGoods(OrderGoods order) {
		String sql = "update xj_shop_order set sj_money=?,service_detail=?,address=? where id=?";
		System.out.println(sql);
		Object[] paras = new Object[] {
				order.getActualFee(),
				order.getServiceDetails(),
				order.getAddressId(),
				order.getOrderId()
		};
		editOrderBySql(sql,paras);
		return order;
	}

}